package com.ssy.lingxi.report.dao.purchase;

import com.ssy.lingxi.report.model.bo.tally.statistics.PurchaseQuotedPriceStatisticsTallyVO;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

/**
 *  采购报价DAO
 * @author ds
 * @date 2021/5/11
 * @version 2.0.0
 */
@Service
public class QuotedPriceDAO {

    @Resource
    @Qualifier("purchaseJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 采购报价-统计数量
     * @return
     */
    public List<PurchaseQuotedPriceStatisticsTallyVO> getStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" create_member_id memberId,create_member_role_id roleId,")
                .append(" sum(case when (interior_state in (11,15,16) )then 1 else 0 end) toBeCommitValifyCount,")
                .append(" sum(case when (interior_state=12 )then 1 else 0 end) toBeValifyStep1Count,")
                .append(" sum(case when (interior_state=13 )then 1 else 0 end) toBeValifyStep2Count,")
                .append(" sum(case when (interior_state =14 )then 1 else 0 end) toBeCommitCount")
                .append(" FROM pu_quoted_price ")
                .append(" group by create_member_id,create_member_role_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PurchaseQuotedPriceStatisticsTallyVO.class));
    }

}
